package model.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.Setor;



public class SetorCRUD implements ISetorDAO{

	static PreparedStatement stm;
	static ResultSet rs;

	public void inserirSetor(Setor novoSetor) throws ClassNotFoundException, SQLException{

			stm = ConectaBanco.getConexaoMySQL().prepareStatement("insert into setor values (?,?,?,?)");
			stm.setInt	 (1, novoSetor.getId()         );
			stm.setString(2, novoSetor.getNome()       );
			stm.setString(3, novoSetor.getResponsavel());
			stm.setInt	 (4, novoSetor.getSala()       );


			System.out.println(stm);
			stm.executeUpdate();


			stm.close();

	}

	@Override
	public void deletarSetor(Setor delSetor) throws ClassNotFoundException,
	SQLException {

		stm = ConectaBanco.getConexaoMySQL().prepareStatement("delete from setor  where id = ?");

		stm.setInt   (1, delSetor.getId());
		
		stm.executeUpdate();

		stm.close();
		

	}


	@Override
	public void alterarSetor(Setor altSetor) throws ClassNotFoundException,
	SQLException {
		

			stm = ConectaBanco.getConexaoMySQL().prepareStatement("update setor set nome = ?, responsavel = ?, sala = ? where id = ?");

			stm.setString(1, altSetor.getNome());
			stm.setString(2, altSetor.getResponsavel());
			stm.setInt   (3, altSetor.getSala());
			stm.setInt   (4, altSetor.getId());
			
			stm.executeUpdate();

			stm.close();
			

	}

	@Override
	public ArrayList<Setor>  listarSetor() throws ClassNotFoundException,
	SQLException {
		ArrayList<Setor> listSetor = new ArrayList<Setor>() ;
		Setor setorTmp;

		stm = ConectaBanco.getConexaoMySQL().prepareStatement("select * from setor");
		rs  = stm.executeQuery();

		while (rs.next()){
			setorTmp = new Setor(rs.getInt("id"), rs.getString("nome"), rs.getString("responsavel"), rs.getInt("sala"));
			listSetor.add(setorTmp);
		}

		stm.close();
		rs.close();

		return  listSetor;
	}
	@Override
	public int proximoIdSetor() throws ClassNotFoundException, SQLException {
		int id = 1;

			stm = ConectaBanco.getConexaoMySQL().prepareStatement("select max(id) as id from setor");
			rs  = stm.executeQuery();
			if (rs.next()){
				id = rs.getInt("id")+1;
			}

			stm.close();
			rs.close();

			return id;
	}



}
